/*
 * CommercialInvoiceDAO.java
 *
 * Created on June 17, 2003
 */

package com.scalar.chiptrack.operations.packingslip.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.operations.packingslip.forms.CommercialInvoiceForm;
import com.scalar.chiptrack.operations.packingslip.forms.PackingSlipForm;
import com.scalar.chiptrack.operations.vendor.VendorAddress;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.sales.Customer;
import com.scalar.chiptrack.sales.SalesOrder;
import com.scalar.chiptrack.sales.LineItem;
import com.scalar.chiptrack.sales.dao.CustomerDAO;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.commons.Constants;


/**
 *
 * @author  Balaram Putta
 * @version
 */
public class CommercialInvoiceDAO
{
    //changed by hareesh on 14th August 2003
    // -- START --
    // Written overloaded method to support dbTranscation

    public static void saveCommercialInvoice( CommercialInvoiceForm commercialInvoiceForm, HashMap lineItemScheduleNumberMap ) throws ScalarException
    {
        saveCommercialInvoice( null, commercialInvoiceForm, lineItemScheduleNumberMap );
    }

    public static void saveCommercialInvoice(DBTransaction dbTranscation, CommercialInvoiceForm commercialInvoiceForm, HashMap lineItemScheduleNumberMap ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;

        try
        {
             if(dbTranscation == null){
                   conn = DataSource.getConnection();
             }else{
                   conn = dbTranscation.getConnection();
             }

            // Step 1
            // Update Commercial Invoice Information
            pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.commercialinvoice.saveCommercialInvoice" ) );
            pstmt.setString( 1, commercialInvoiceForm.getCountryOfOrigin() );
            pstmt.setString( 2, commercialInvoiceForm.getPortOfLanding() );
            pstmt.setString( 3, commercialInvoiceForm.getPortOfDestination() );
            pstmt.setString( 4, commercialInvoiceForm.getLicenseSymbol() );
            pstmt.setString( 5, commercialInvoiceForm.getEccn() );
            pstmt.setString( 6, commercialInvoiceForm.getComments() );
            pstmt.setString( 7, commercialInvoiceForm.getCountryOfFabOrigin() );
            pstmt.setString( 8, commercialInvoiceForm.getCountryOfAssyOrigin() );
            pstmt.setInt( 9, commercialInvoiceForm.getCustomerShippingId() );

            pstmt.executeUpdate();

            pstmt.close();
            pstmt = null;

            // Step 2
            // Update Schedule Number into Customer_Shipping_Line_Item_Table
            if( ( lineItemScheduleNumberMap != null ) && ( lineItemScheduleNumberMap.isEmpty() != true ) )
            {
                pstmt = conn.prepareStatement( SqlQueryMappings.get ( "operations.packingslip.updateLineItemScheduleNumber" ) );
                Iterator lineItemIdIterator = lineItemScheduleNumberMap.keySet().iterator();
                String lineItemId = null;
                String scheduleNumber = null;
                while( lineItemIdIterator.hasNext() )
                {
                    lineItemId = ( String ) lineItemIdIterator.next();
                    scheduleNumber = ( String ) lineItemScheduleNumberMap.get( lineItemId );

                    pstmt.setString( 1, scheduleNumber );
                    pstmt.setString( 2, lineItemId );
                    pstmt.setInt( 3, commercialInvoiceForm.getCustomerShippingId() );
                    pstmt.addBatch();
                }

                pstmt.executeBatch();
            }
            success = true;
        }
        catch( Exception sqle )
        {
            throw new ScalarException( "Could not update Commercial Invoice into database", sqle );
        }
        finally
        {
            try
            {
                if (dbTranscation == null) {
                    if (conn != null)
                    {
                        if (success == true)
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
            }
            catch (SQLException ignore) {}

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }
    // -- END --

    public static CommercialInvoiceForm loadCommercialInvoice( int customerShippingId ) throws ScalarException
    {
        CommercialInvoiceForm commercialInvoiceForm = null;
        Connection conn 		= null;
        PreparedStatement pstmt = null;
        ResultSet rset 			= null;
        String label 	        = "";
        int noOfBoxes 	        = 0;
        String weight 	        = "";
        String originCountry    = "";
        String contactPerson    = "";
        String landingPort 	    = "";
        String destPort 	    = "";
        String licensSymbol	    = "";
        String eccn			    = "";
        int formAddressId	    = 0;
        String fob 			    = "";
        String status 		    = "";
        int toAddressId		    = 0;
        String shippingContactPerson = "";
        String awBillNo		    = "";
        String comments		    = "";
        String shippedVia       = "";
        int billingAddrId       = 0 ;
        int vendorLocationId    = 0 ;
        String packedby         = "";
        String title            = "";
        String tempStr          = null;
        String lotNumbersStr    = null;
        String supplierNo       = "";
        String odmSupplierNo    = "";
        int odmId               = 0;
        String shipDate         = "";
        String inv_no           = "";
        String grossWeight      = "";
        String uomWeight        = "";
        String countryOfFabOrigin = null;
        String countryOfAssyOrigin = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.commercialinvoice.loadCommercialInvoice" ) );
            pstmt.setInt( 1, customerShippingId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                label 			    = rset.getString( "Packing_Slip_Number" );
                noOfBoxes 		    = rset.getInt( "CST_Box_Count" );
                weight 			    = rset.getString ( "CST_Weight" );
                originCountry 	    = rset.getString( "CST_CI_Origin_Country" );
                landingPort 	    = rset.getString( "CST_CI_Landing_Port" );
                destPort 	 	    = rset.getString( "CST_CI_Dest_Port" );
                licensSymbol	    = rset.getString( "CST_CI_License_Symbol" );
                eccn			    = rset.getString( "CST_CI_ECCN" );
                formAddressId	    = rset.getInt( "CST_From_Address_ID" );
                fob 			    = rset.getString( "CST_FOB" );
                status 		 	    = rset.getString( "CST_Status" );
                toAddressId		    = 0;
                toAddressId         = rset.getInt( "CST_To_Address_ID" );
                contactPerson 	    = rset.getString( "CST_Shipping_Contact_Person" );
                //shippingContactPerson = rset.getString("CST_Shipping_Contact_Person");
                awBillNo		    = rset.getString ( "CST_AW_Bill_Number" );
                comments		    = rset.getString( "CST_CI_Comments" );
                billingAddrId       = rset.getInt( "CST_Billing_Address_Id" );
                vendorLocationId    = rset.getInt ( "CST_Location_Address_Id" );
                shippedVia		    = rset.getString( "CST_Method" );
                //int customerId    = rset.getInt("Customer_ID");
                //int odmId         = rset.getInt("ODM_ID");
                packedby            =   rset.getString( "CST_Packedby" );
                title               =   rset.getString( "CST_Title" );
                supplierNo          = rset.getString ( "CST_Supplier_Number" );
                odmSupplierNo       = rset.getString ( "CST_ODM_Supplier_Number" );
                odmId               = rset.getInt( "CST_ODM_ID" );
                shipDate            = rset.getString ( "SHIP_DATE" );
                inv_no              = rset.getString("INV_NO");
                grossWeight         = rset.getString("CST_Gross_Weight");
                uomWeight           = rset.getString("CST_UOM_Weight");
                countryOfFabOrigin  = rset.getString("CST_CI_COUNTRY_OF_FAB_ORIGIN");
                countryOfAssyOrigin = rset.getString("CST_CI_COUNTRY_OF_ASSY_ORIGIN");

                if ( (contactPerson == null) || ((contactPerson != null)&&(contactPerson.equalsIgnoreCase("null"))))
                {
                    contactPerson = "";
                }

                commercialInvoiceForm = new CommercialInvoiceForm( customerShippingId,
                                                                   label,
                                                                   noOfBoxes,
                                                                   weight,
                                                                   originCountry,
                                                                   landingPort,
                                                                   destPort,
                                                                   licensSymbol,
                                                                   eccn,
                                                                   fob );

                String[] tracking =  StringUtils.toStringArray( awBillNo, ";" );
                if ( tracking == null ||  tracking.length == 0)
                {
                    tracking = new String[]{""};
                }
                commercialInvoiceForm.setFrieghtTrackingNumber( tracking );
                commercialInvoiceForm.setStatus( status );

                commercialInvoiceForm.setContactPerson(contactPerson);

                commercialInvoiceForm.setShipperAddress( VendorDAO.getVendorNameAndAddress( formAddressId ) );
                commercialInvoiceForm.setVendorLocationId( vendorLocationId );
                commercialInvoiceForm.setComments( comments );

                commercialInvoiceForm.setShippedVia( shippedVia );
                commercialInvoiceForm.setPackedby( packedby );
                commercialInvoiceForm.setTitle( title);
                commercialInvoiceForm.setShipDate( shipDate );

                commercialInvoiceForm.setShippingId(toAddressId);
                commercialInvoiceForm.setShippingAddressObject(CustomerDAO.getAddress( toAddressId ) );
                commercialInvoiceForm.setShippingAddressAsString( commercialInvoiceForm.getShippingAddressObject().getAddressAsString ( ) );

                commercialInvoiceForm.setShipperId( formAddressId );
                commercialInvoiceForm.setShipperAddressObject( CustomerDAO.getAddress( formAddressId ) );
                commercialInvoiceForm.setShipperAddressAsString( SalesOrder.getAddressAsString ( CustomerDAO.getAddress( formAddressId ) ) );

                /*HashMap lineItemMap = NewPackingSlipDAO.loadLotNumberDataByCustomerShippingId( customerShippingId);

                Iterator iterator = lineItemMap.keySet().iterator();

                while( iterator.hasNext() )
                {
                   String key = (String)iterator.next();

                   ArrayList fidList = (ArrayList)lineItemMap.get(key);

                   Iterator fidListIterator = fidList.iterator();
                   while( fidListIterator.hasNext() )
                   {
                        FinalInventoryData fid = (FinalInventoryData)fidListIterator.next();
                        tempStr = fid.getLotNumber();
                        if( lotNumbersStr != null )
                        {
                            lotNumbersStr =  lotNumbersStr + "," + "'" + tempStr + "'";
                        }
                        else
                        {
                           lotNumbersStr = "'" + tempStr + "'" ;
                        }
                    }
                }*/

                //commercialInvoiceForm.setCountryOfFabOrigin( VendorDAO.getVendorOrigins( lotNumbersStr, Constants.LOCATION_TYPE_FAB ));

                //commercialInvoiceForm.setCountryOfFabOrigin( getFabAssmCountryOfOrigin( customerShippingId, "FAB" ));
                //commercialInvoiceForm.setCountryOfAssyOrigin( getFabAssmCountryOfOrigin( customerShippingId, "ASSM" ) );
                if(StringUtils.isNullOrEmpty(countryOfFabOrigin)) {
                    countryOfFabOrigin = loadCountryOfOrigin( null, customerShippingId, Constants.LOCATION_TYPE_FAB );
                }
                if(StringUtils.isNullOrEmpty(countryOfAssyOrigin)) {
                    countryOfAssyOrigin = loadCountryOfOrigin( null, customerShippingId, Constants.LOCATION_TYPE_ASSEMBLY );
                }
                commercialInvoiceForm.setCountryOfFabOrigin( countryOfFabOrigin );
                commercialInvoiceForm.setCountryOfAssyOrigin( countryOfAssyOrigin );

                commercialInvoiceForm.setCommercialInvoiceNo( inv_no );
                commercialInvoiceForm.setGrossWeight( grossWeight );
                commercialInvoiceForm.setWeightType( uomWeight );


                if ( billingAddrId > 0)
                {
                    commercialInvoiceForm.setBillingId(billingAddrId);
                }
                /*
                else
                {
                    //We are Assigning ShippingFromAddressId to  BillingAddressId because
                    //new column called  CST_Billing_Address_Id is added to CST table on 26 May 2004
                    //So Previously 'Billing Address' used to get from ShippingFromAddressId DB Field
                    //But now we are getting from For CST_Billing_Address_Id DB Field, so prvious PS records we fixed like this
                    commercialInvoiceForm.setBillingId(formAddressId);
                }
                commercialInvoiceForm.setBillingAddressObject(CustomerDAO.getAddress( commercialInvoiceForm.getBillingId() ));
                commercialInvoiceForm.setBillingAddress( SalesOrder.getAddressAsString (  CustomerDAO.getAddress( commercialInvoiceForm.getBillingId() ) ) );
                */
                VendorAddress va=null;
                if ( vendorLocationId > 0)
                {
                    va = VendorDAO.getVendorNameAndAddress( vendorLocationId );
                }
                else
                {
                    //We are Assigning ShippingFromLocationId to  ShippingFromLocationId because
                    //new column called  CST_Location_Address_Id is added to CST table on 26 May 2004
                    //So Previously 'Location Id' used to get from ShippingFromAddressId DB Field
                    //But now we are getting from For CST_Location_Address_Id DB Field, so prvious PS records we fixed like this
                    va = VendorDAO.getVendorNameAndAddress( formAddressId );
                }
                commercialInvoiceForm.setVendorName(va.getVendorName());
                va=null;
                Customer customer = new Customer();

                /*customer.setCustomerId( customerId );
                commercialInvoiceForm.setOdm(CustomerDAO.getCustomerName( null, odmId ));*/


                commercialInvoiceForm.setSupplierNumber( supplierNo );
                commercialInvoiceForm.setOdmSupplierNumber( odmSupplierNo );


                if ( odmId > 0 )
                {
                    Customer odmCustomer = CustomerDAO.loadCustomerById ( odmId );
                    commercialInvoiceForm.setOdm( odmCustomer.getCustomerName() );
                }


                /*CustomerDAO.getCustomerDetailsForAcknowledgement( null, customer );
                commercialInvoiceForm.setSupplierNumber( customer.getSupplierNumber() );
                if ( odmId > 0 )
                {
                    customer.setCustomerId( odmId );
                    CustomerDAO.getCustomerDetailsForAcknowledgement( null, customer );
                    commercialInvoiceForm.setOdmSupplierNumber( customer.getSupplierNumber() );
                }
                else
                {
                    commercialInvoiceForm.setOdmSupplierNumber( "" );
                } */

                //Ends Here

                //int temp = rset.getInt("CST_To_Address_ID");
                 //commercialInvoiceForm.setShippingAddress(CustomerDAO.getAddress(temp));
            }
        }
        catch( SQLException sqle )
        {
            throw new ScalarException( "Could not retrieve Commercial Invoice from database", sqle );
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }

            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }

            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return commercialInvoiceForm;
    }

    // changed by hareesh on 29th July 2003
    // -- STRAT --
    public static void deleteCommercialInovice( int customerShippingId ) throws ScalarException
    {
            deleteCommercialInovice(null, customerShippingId );
    }
    // Balaram, June 25, 2003
    public static void deleteCommercialInovice(DBTransaction dbTransaction, int customerShippingId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        boolean success = false;

        try
        {
            //conn = DataSource.getConnection();
            if(dbTransaction != null){
                conn = dbTransaction.getConnection();
            } else{
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.commercialinvoice.deleteCommercialInovice" ) );
            pstmt.setInt( 1, customerShippingId );
            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            throw new ScalarException( "Could not delete Commercial Invoice for Customer Shipping Id : " + customerShippingId, sqle );
        }
        finally
        {
            // Commit/Rollback here it selef -- if its not in a manual transaction
            if (dbTransaction == null) {
                try
                {
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                catch (SQLException ignore) {}
            }
            /*try {
                if (conn != null) {
                    if (success == true) {
                        conn.commit();
                    }
                    else {
                        conn.rollback();
                    }
                }
            }
            catch (SQLException ignore) {}*/
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    // -- END --

    // Balaram, June 25, 2003
    public static ArrayList loadSalesOrderIdAndNumberList() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList salesOrderList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.commercialinvoice.loadSalesOrderIdAndNumberList" ) );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                salesOrderList.add( new org.apache.struts.util.LabelValueBean( rset.getString( "SalesOrder_No" ), rset.getString( "SalesOrder_ID" ) ) );
            }
        }
        catch (Exception e)
        {
            throw new ScalarException ("Could not retrieve sales order IDs and numbers from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return salesOrderList;
    }


    public static ArrayList loadSalesOrderCustomerPOList() throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList list = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.commercialinvoice.loadSalesOrderCustomerPOList" ) );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                list.add( new org.apache.struts.util.LabelValueBean( rset.getString( "SO_Customer_PO_Number" ), rset.getString( "SalesOrder_ID" ) ) );
            }
        }
        catch (Exception e)
        {
            throw new ScalarException( "Could not retrieve Sales Orders and Customer POs from database", e );
        }
        finally {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return list;
    }



    public static ArrayList loadPsBySoIdForUndeletedCommercialInvoices( int salesOrderId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        ArrayList packingSlipList = new ArrayList();

        try {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.commercialinvoice.loadPsBySoIdForUndeletedCommercialInvoices" ) );
            pstmt.setInt( 1, salesOrderId );

            rset = pstmt.executeQuery();

            PackingSlipForm packingSlipForm = null;

            while( rset.next() )
            {
                packingSlipForm = new PackingSlipForm();

                packingSlipForm.setCustomerShippingId( rset.getInt( "CustomerShipping_ID" ) );
                packingSlipForm.setLabel( rset.getString( "Label" ) );

                packingSlipList.add( packingSlipForm );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException ("Could not retrieve PackingSlip list from database for salesOrder ID: " + salesOrderId, e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return packingSlipList;
    }

    /**
     * @return  HashMap
     */
    public static HashMap loadCommercialInvoiceSettings() throws ScalarException
    {
        HashMap settings = new HashMap();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        try {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( SqlQueryMappings.get( "operations.commercialinvoice.loadDefaultSettingOfCommercialInvoices" ) );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                Float maxPrice = new Float(0.10f);
                Float minPrice = new Float( 0.10f );
                Float minTotal = new Float(1.0f);
                String bool = "1";
                try
                {
                    maxPrice = new Float( rset.getFloat("Max_Unit_Price") );
                }
                catch(NumberFormatException nfe)
                {
                    maxPrice = new Float(0.10f);
                }

                try
                {
                    minTotal = new Float( rset.getFloat("Min_Grand_Total") );
                }
                catch(NumberFormatException nfe)
                {
                    minTotal = new Float(1.0f);
                }

                try
                {
                    bool = rset.getString("Adjust_Flag");
                }
                catch(Exception e)
                {
                    bool = "1";
                }

                try
                {
                    minPrice = new Float( rset.getFloat("Min_Unit_Price") );
                }
                catch(NumberFormatException nfe)
                {
                    minPrice = new Float(0.10f);
                }
                settings.put("Min_Unit_Price" , minPrice );
                settings.put("Max_Unit_Price" , maxPrice );
                settings.put("Min_Grand_Total" , minTotal );
                settings.put("Adjust_Flag" , bool );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException ();
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return settings;
    }

    
    public static String getFabAssmCountryOfOrigin( int customerShippingId, String Process ) throws ScalarException
    {
        Connection conn             = null;
        CallableStatement cstmt     = null;
        ResultSet rset              = null;
        String fabCountryOfOrigin   = null;

        try
        {
            conn = DataSource.getConnection();

            cstmt = conn.prepareCall( "{ call SP_FETCH_FAB_ASSM_LOCATIONS( ?, ? ) }" );

            cstmt.setInt(1, customerShippingId );
            cstmt.setString( 2, Process );
            
            rset = cstmt.executeQuery();

            while (rset.next())
            {
               fabCountryOfOrigin = rset.getString("l_VENDOR_NAME");
            }
        }
        catch (Exception e)
        {
            throw new ScalarException("Could not retrieve fabCountryOfOrigin : " , e);
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (cstmt != null)
            {
                try
                {
                    cstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return fabCountryOfOrigin;
    }

    /**
     *
     * @param lineItem
     * @return
     * @throws ScalarException
     */
    public static LineItem getAssemblyCountryOfOrigin(LineItem lineItem ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        String fabCountryOfOrigin   = null;

        try
        {
            conn = DataSource.getConnection();

            pstmt = conn.prepareStatement(SqlQueryMappings.get("SELECT distinct l_VENDOR_NAME FROM location where location_id in \n" +
                    "\t( select location_id from process_step_location_table where processstep_id in \n" +
                    "\t\t( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( ? ) WHERE PS_TYPE = ?  ) )"));

            pstmt.setInt(1, lineItem.getProcessStep_Id() );
            pstmt.setString(2, "Assembly" );
            rset = pstmt.executeQuery();

            while (rset.next())
            {
               fabCountryOfOrigin = fabCountryOfOrigin + "," + rset.getString("l_VENDOR_NAME");
            }
            lineItem.setFabCountryOfOrigin( fabCountryOfOrigin);
        }
        catch (Exception e)
        {
            throw new ScalarException("Could not retrieve fabCountryOfOrigin : " , e);
        }
        finally
        {
            if (rset != null)
            {
                try
                {
                    rset.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (pstmt != null)
            {
                try
                {
                    pstmt.close();
                }
                catch (SQLException ignore)
                {
                }
            }
            if (conn != null)
            {
                try
                {
                    conn.close();
                }
                catch (SQLException ignore)
                {
                }
            }
        }
        return lineItem;
    }

    public static String loadCountryOfOrigin( DBTransaction dbTransaction, int customerShippingId, String psType ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        String countryOfOrigin      = "";

        try
        {
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT COUNTRY FROM CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, CUSTOMER_SHIPPING_LOT_NUMBER_TABLE CSLNT, LOT_TABLE LT1, LOT_TABLE LT2, LOCATION L, ADDRESS A WHERE CSLIT.CSLIT_ID = CSLNT.CSLIT_ID AND CSLNT.LOT_ID = LT1.LOT_ID AND CSLNT_QTY_PULL > 0 AND LT1.FAB_LOT_NUMBER = LT2.FAB_LOT_NUMBER AND LT2.LOCATION_ID = L.LOCATION_ID AND L.ADDRESS_ID = A.ADDRESS_ID AND CUSTOMERSHIPPING_ID = ? AND L_TYPE = ?" );

            pstmt.setInt    ( 1, customerShippingId );
            pstmt.setString ( 2, psType );

            rset = pstmt.executeQuery();

            while (rset.next())
            {
               countryOfOrigin = countryOfOrigin + rset.getString("COUNTRY") + ",";
            }

            if( countryOfOrigin != null && countryOfOrigin.trim().length() > 0 )
            {
                countryOfOrigin = countryOfOrigin.substring( 0, ( countryOfOrigin.length() - 1 ) );
            }
        }
        catch (Exception e)
        {
            throw new ScalarException("Could not retrieve Country Of Origin : ", e);
        }
        finally
        {
            if (rset != null)
            {
                try{ rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null)
            {
                try{ pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null)
            {
                try{ conn.close(); } catch (SQLException ignore){}
            }
        }
        return countryOfOrigin;
    }

}
